<?php if ( ! defined('BASEPATH')) exit('No direct script access allowed');

class Misc_model extends CI_Model {
	
	function __construct()
    {
        // Call the Model constructor
        parent::__construct();
    }
	
	function get_address_by_id($id)
	{
		$sql = "SELECT * FROM `address` WHERE `id`='$id'";
		$query = $this->db->query($sql);
		if($query->num_rows() > 0)
			return $query->row();
		return FALSE;
	}
	
	function get_address_str_by_id($id)
	{
		if(($addr = $this->get_address_by_id($id)) == FALSE)
			return FALSE;
		
		$level = $addr->level;
		
		$concat = 'CONCAT_WS(\' \', ';
		for($i = 1; $i <= $level; $i++)
			$concat .= "`a{$i}`.`name`,";
		$concat = substr($concat, 0, -1).')';
		
		$sql = "SELECT {$concat} AS `str` FROM `address` AS `a1`";
		for($i = 2; $i <= $level; $i++)
			$sql .= " JOIN `address` AS `a{$i}` ON `a".($i-1)."`.`id` = `a{$i}`.`belongs_to` AND `a{$i}`.`level` = {$i}";
		$sql .= " WHERE `a{$level}`.`id` = '$id'";
		$query = $this->db->query($sql);
		if($query->num_rows() > 0){
			$row = $query->row();
			return $row->str;
		}
		return FALSE;
	}
	
	function get_address_ids_by_components($data)
	{
		$level = count($data);
		
		if(empty($data) || $level < 3)
			return FALSE;
		
		$select = 'a1.id AS a1';
		$join = '';
		$where = "a1.name = '{$data[0]}'";
		
		for($i=2; $i<=$level; $i++)
		{
			$select .= ", `a{$i}`.id AS a{$i}";
			$join 	.= " JOIN `address` AS `a{$i}` ON `a{$i}`.`belongs_to` = `a".($i-1)."`.`id` ";
			$where	.= " AND `a{$i}`.name = '".$data[$i-1]."' ";
		}
			
		$sql = "SELECT $select FROM address AS a1 $join WHERE $where;";
		
		$query = $this->db->query($sql);
		if($query->num_rows() > 0)
		{
			$row = $query->row();
			$res = array();
			foreach($row as $i)
			{
				$res[] = $i;
			}
			return $res;
		}
		return FALSE;
	}
	
	/**
	 * 같은 단위의 행정구역 목록 리턴
	 * 탐색 실패시에도 FALSE 아닌 빈 배열 리턴
	 * @param	string	행정구역 ID
	 * @return 	array
	 */
	function get_address_siblings($id)
	{
		$sql = "
			SELECT `addr_sib`.* 
			FROM `address` AS `addr_sib`  
			INNER JOIN `address` AS `addr_me` ON `addr_sib`.`belongs_to` = `addr_me`.`belongs_to` 
			WHERE `addr_me`.`id`='$id'
			GROUP BY `addr_sib`.`id`
			ORDER BY `addr_sib`.`name` ASC
		";
		$query = $this->db->query($sql);
		if($query->num_rows() > 0)
		{
			return $query->result();
		}
		return array();
	}
	
	/**
	 * 행정구역 ID를 입력받아 한 단계 밑의 행정구역의 목록을 리턴한다.
	 * ADDRESS_NO_PARENT(='0000000000')를 입력하면 최상위 주소 출력 (config/constants.php 참조)
	 * @author 박지수
	 * @param	String	상위 행정구역의 ID
	 * @return 	Array
	 */
	function get_address_children($id)
	{
		if( is_numeric($id) && strlen($id) == ADDRESS_ID_LENGTH ){
			$sql = "
				SELECT `id`, `name`, `lat`, `lng` 
				FROM `address` 
				WHERE `belongs_to` = '$id'
				GROUP BY `id`
				ORDER BY `name` ASC
			";
			$query = $this->db->query($sql);
			if($query->num_rows() > 0){
				return $query->result();
			}
		}
		return FALSE;
	}
	
	/**
	 * 행정구역 ID를 받아 동위 및 상위 주소를 포함하는 배열 생성
	 * @param	String
	 * @return 	Array
	 */
	function create_address_array($id = ADDRESS_NO_PARENT)
	{
		$res = array(
			array('' => '---도/시----'),
			array('' => '--시/군/구--'),
			array('' => '--동/읍/면--')
		);
		
		$tmp = $this->get_address_children(ADDRESS_NO_PARENT, TRUE);
		foreach($tmp as $row)
			$res[0][$row->id] = $row->name; 
		
		if($id != ADDRESS_NO_PARENT)
		{
			$addr = $this->get_address_by_id($id);
			if($addr){
				$level = $addr->level;
				if($level > 1)
				{
					$tmp = $this->get_address_siblings($id);
					foreach($tmp as $row)
						$res[$level-1][$row->id] = $row->name; 
				}
				if($level < 3)
				{
					$tmp = $this->get_address_children($addr->id);
					foreach($tmp as $row)
						$res[$level][$row->id] = $row->name; 
				}
					
			}	
		}
		return $res;
	}
	
	/**
	 * GPS Location 생성
	 * @param	Array	address_id, addres_str, lat, long, radius
	 * @param	Int		inserted ID
	 */
	function create_location($data)
	{
		$keys = implode("`,`", array_keys($data));
		$values = implode("','", array_values($data));
		
		$sql = "INSERT INTO location_gps(`$keys`) VALUES ('$values');";
		$query = $this->db->query($sql);
		if(mysql_affected_rows() > 0)
		{
			$id_query = $this->db->query('SELECT LAST_INSERT_ID() as `id`');
			$res = $id_query->row();
			return $res->id;
		}
	}
	
	function get_locations_by_str($str)
	{
		$sql = "
			SELECT SQL_CALC_FOUND_ROWS * FROM `location_gps`
			WHERE `address_str` LIKE '%{$str}%'
			GROUP BY `id`
			ORDER BY `id` DESC
			LIMIT 5
		";
		$query = $this->db->query($sql);
		$res = new stdClass;
		$res->locations = $query->result();
		
		$query = $this->db->query("SELECT FOUND_ROWS() AS rows");
		$res->count = $query->row()->rows;
		return $res;
	}
	
	/**
	 * 행정구역 ID 입력받아 하위 Location 목록 리턴
	 * @param	String	Address ID
	 * @return 	Array	Location 없으면 빈 Array
	 */
	function get_locations_by_address_id($id)
	{
		$sql = "
			SELECT * FROM `address` AS `a1`
			JOIN `address` AS `a2` ON `a1`.`id` = `a2`.`belongs_to`
			JOIN `address` AS `a3` ON `a2`.`id` = `a3`.`belongs_to`
			WHERE `a1`.`id` = '$id' OR `a2`.`id` = '$id' OR `a3`.`id` = '$id'
		";
		$query = $this->db->query($sql);
		if($query->num_rows() > 0)
			return $query->result();

		return array();
	}
	
	function get_location_by_address($addr)
	{
		$sql = "
			SELECT `a3`.* FROM `address` AS `a3`
			JOIN `address` AS `a2` ON `a2`.`id` = `a3`.`belongs_to`
			WHERE `a3`.`name` = '{$addr[2]}' AND `a2`.`name` = '{$addr[1]}'
		";
		$query = $this->db->query($sql);
		if($query->num_rows() > 0)
			return $query->row();
		return FALSE;
	}
	
	function increase_view_count($id)
	{
		$sql = "
			UPDATE `poll_common`
			SET view_count = view_count+1
			WHERE id = '$id'
		";
		$query = $this->db->query($sql);
		return mysql_affected_rows();
	}
	
	function get_all_location()
	{
		$sql = "SELECT * FROM `location_gps`";
		$query = $this->db->query($sql);
		if($query->num_rows() > 0)
			return $query->result();
		return FALSE;
	}
	
	function get_active_session_from_db()
	{
		$sql = "
			SELECT * FROM ci_sessions
			WHERE last_activity > UNIX_TIMESTAMP() - 7200 
		";
		$query = $this->db->query($sql);
		if($query->num_rows() > 0)
			return $query->result();
		return array();;
	}
}